from sqlalchemy import Index, text
from sqlalchemy import PrimaryKeyConstraint, Sequence, UniqueConstraint
from sqlalchemy.dialects.postgresql import TIMESTAMP

from tzq.extensions import db

"""
    数据库比对系统，比对任务类。
"""


class DatabaseInfo(db.Model):
    """ 数据库信息表 """
    __tablename__ = 'dc_database_info_t'
    __table_args__ = (
        PrimaryKeyConstraint('db_info_id', name='dc_database_info_pk', comment='主键'),
        UniqueConstraint('connect_name', name='uc_dc_database_info_1',
                         comment='唯一约束：根据connect_name(数据库连接名称)来进行唯一约束。'),
        # 创建一个带where条件的索引
        Index(
            'uidx_dc_database_info_1',
            'connect_name',
            unique=True,
            postgresql_where=text("delete_flag = 'N'")  # WHERE 条件
        ),
        {'comment': '数据库信息表', },
    )
    db_info_id = db.Column(
        db.BigInteger,
        Sequence('dc_database_info_s'),
        primary_key=True,
        server_default=Sequence('dc_database_info_s').next_value(),
        nullable=False,
        comment='主键ID'
    )
    connect_name = db.Column(db.String(1000), nullable=False, comment='数据库连接名称')
    database_type = db.Column(db.String(1000), nullable=False, comment='数据库类型')
    host = db.Column(db.String(1000), nullable=False, comment='集群主机IP地址')
    port = db.Column(db.BigInteger, nullable=False, comment='集群主机端口地址')
    dbname = db.Column(db.String(1000), nullable=False, comment='数据库名称')
    username = db.Column(db.String(1000), nullable=False, comment='用户名')
    password = db.Column(db.String(1000), nullable=False, comment='密码')
    source = db.Column(db.String(255), comment='来源')
    comment = db.Column(db.String(4000), comment='注释')
    delete_flag = db.Column(db.String(10), nullable=False, server_default='N', comment='软删除标记。是：“Y”；否：“N”。')
    created_by = db.Column(db.BigInteger, nullable=False, server_default='-1', comment='创建人user_id')
    creation_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                              server_default=db.func.clock_timestamp(), comment='创建时间')
    last_updated_by = db.Column(db.BigInteger, nullable=False, server_default='-1', comment='最后更新人user_id')
    last_update_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                                 server_default=db.func.clock_timestamp(), comment='最后更新时间')
    sys_description = db.Column(db.String(4000), comment='系统描述')

    def to_dict(self):
        return {
            'db_info_id': self.db_info_id,
            'connect_name': self.connect_name,
            'database_type': self.database_type,
            'host': self.host,
            'port': self.port,
            'dbname': self.dbname,
            'username': self.username,
            'password': self.password,
            'source': self.source,
            'comment': self.comment,
            'delete_flag': self.delete_flag,
            'created_by': self.created_by,
            'creation_date': self.creation_date,
            'last_updated_by': self.last_updated_by,
            'last_update_date': self.last_update_date.strftime('%Y-%m-%d %H:%M:%S'),
            'sys_description': self.sys_description,
        }


class CompareTask(db.Model):
    """ 数据库比对任务表 """
    __tablename__ = 'dc_comp_task_t'
    __table_args__ = (
        PrimaryKeyConstraint('comp_task_id', name='dc_comp_task_pk', comment='主键'),
        UniqueConstraint('comp_task_name', name='uc_comp_task_1',
                         comment='唯一约束：根据 comp_task_name (数据库比对任务名称)来进行唯一约束。'),
        # 创建一个带where条件的索引
        Index(
            'uidx_comp_task_1',
            'comp_task_name',
            unique=True,
            postgresql_where=text("delete_flag = 'N'")  # WHERE 条件
        ),
        {'comment': '数据库比对任务表', },
    )
    comp_task_id = db.Column(
        db.BigInteger,
        Sequence('dc_comp_task_s'),
        primary_key=True,
        server_default=Sequence('dc_comp_task_s').next_value(),
        nullable=False,
        comment='主键ID'
    )
    comp_task_name = db.Column(db.String(300), nullable=False)
    db_info_1_id = db.Column(db.BigInteger, nullable=False)
    db_info_2_id = db.Column(db.BigInteger, nullable=False)
    db_info_3_id = db.Column(db.BigInteger)
    db_info_4_id = db.Column(db.BigInteger)
    db_info_5_id = db.Column(db.BigInteger)
    db_info_6_id = db.Column(db.BigInteger)
    db_info_7_id = db.Column(db.BigInteger)
    comment = db.Column(db.String(4000), comment='注释')
    delete_flag = db.Column(db.String(10), nullable=False, server_default='N', comment='软删除标记。是：“Y”；否：“N”。')
    created_by = db.Column(db.BigInteger, nullable=False, server_default='-1', comment='创建人user_id')
    creation_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                              server_default=db.func.clock_timestamp(), comment='创建时间')
    last_updated_by = db.Column(db.BigInteger, nullable=False, server_default='-1', comment='最后更新人user_id')
    last_update_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                                 server_default=db.func.clock_timestamp(), comment='最后更新时间')
    sys_description = db.Column(db.String(4000), comment='系统描述')

    def to_dict(self):
        return {
            'comp_task_id': self.comp_task_id,
            'comp_task_name': self.comp_task_name,
            'db_info_1_id': self.db_info_1_id,
            'db_info_2_id': self.db_info_2_id,
            'db_info_3_id': self.db_info_3_id,
            'db_info_4_id': self.db_info_4_id,
            'db_info_5_id': self.db_info_5_id,
            'db_info_6_id': self.db_info_6_id,
            'db_info_7_id': self.db_info_7_id,
            'comment': self.comment,
            'delete_flag': self.delete_flag,
            'created_by': self.created_by,
            'creation_date': self.creation_date.strftime('%Y-%m-%d %H:%M:%S'),
            'last_updated_by': self.last_updated_by,
            'last_update_date': self.last_update_date.strftime('%Y-%m-%d %H:%M:%S'),
            'sys_description': self.sys_description
        }


class CompareTableJobResult(db.Model):
    """ 数据库表结构比对任务结果表 """
    __tablename__ = 'dc_comp_tab_job_result_t'
    __table_args__ = (
        PrimaryKeyConstraint('result_id', name='dc_comp_tab_job_result_pk', comment='主键'),
        Index(
            'idx_dc_comp_tab_job_result_1',
            'comp_task_id'
        ),
        {'comment': '数据库表结构比对任务结果表', },
    )
    result_id = db.Column(
        db.BigInteger,
        Sequence('dc_comp_tab_job_result_s'),
        primary_key=True,
        server_default=Sequence('dc_comp_tab_job_result_s').next_value(),
        nullable=False,
        comment='主键ID'
    )
    comp_task_id = db.Column(db.BigInteger)
    comp_task_name = db.Column(db.String(300))
    schema = db.Column(db.String(100))
    table_name = db.Column(db.String(100))
    column_name = db.Column(db.String(100))
    total_check_result = db.Column(db.String(100))
    table_name_check_result = db.Column(db.String(100))
    table_name1 = db.Column(db.String(100))
    table_name2 = db.Column(db.String(100))
    table_name3 = db.Column(db.String(100))
    table_name4 = db.Column(db.String(100))
    table_name5 = db.Column(db.String(100))
    table_name6 = db.Column(db.String(100))
    table_name7 = db.Column(db.String(100))
    column_name_check_result = db.Column(db.String(100))
    column_name1 = db.Column(db.String(100))
    column_name2 = db.Column(db.String(100))
    column_name3 = db.Column(db.String(100))
    column_name4 = db.Column(db.String(100))
    column_name5 = db.Column(db.String(100))
    column_name6 = db.Column(db.String(100))
    column_name7 = db.Column(db.String(100))
    data_type_check_result = db.Column(db.String(100))
    data_type1 = db.Column(db.String(100))
    data_type2 = db.Column(db.String(100))
    data_type3 = db.Column(db.String(100))
    data_type4 = db.Column(db.String(100))
    data_type5 = db.Column(db.String(100))
    data_type6 = db.Column(db.String(100))
    data_type7 = db.Column(db.String(100))
    data_default_check_result = db.Column(db.String(100))
    data_default1 = db.Column(db.String(2000))
    data_default2 = db.Column(db.String(2000))
    data_default3 = db.Column(db.String(2000))
    data_default4 = db.Column(db.String(2000))
    data_default5 = db.Column(db.String(2000))
    data_default6 = db.Column(db.String(2000))
    data_default7 = db.Column(db.String(2000))
    pkey_check_result = db.Column(db.String(100))
    pkey1 = db.Column(db.String(100))
    pkey2 = db.Column(db.String(100))
    pkey3 = db.Column(db.String(100))
    pkey4 = db.Column(db.String(100))
    pkey5 = db.Column(db.String(100))
    pkey6 = db.Column(db.String(100))
    pkey7 = db.Column(db.String(100))
    nullable_check_result = db.Column(db.String(100))
    nullable1 = db.Column(db.String(100))
    nullable2 = db.Column(db.String(100))
    nullable3 = db.Column(db.String(100))
    nullable4 = db.Column(db.String(100))
    nullable5 = db.Column(db.String(100))
    nullable6 = db.Column(db.String(100))
    nullable7 = db.Column(db.String(100))
    table_comment_check_result = db.Column(db.String(100))
    table_comment1 = db.Column(db.Text)
    table_comment2 = db.Column(db.Text)
    table_comment3 = db.Column(db.Text)
    table_comment4 = db.Column(db.Text)
    table_comment5 = db.Column(db.Text)
    table_comment6 = db.Column(db.Text)
    table_comment7 = db.Column(db.Text)
    column_comment_check_result = db.Column(db.String(100))
    column_comment1 = db.Column(db.Text)
    column_comment2 = db.Column(db.Text)
    column_comment3 = db.Column(db.Text)
    column_comment4 = db.Column(db.Text)
    column_comment5 = db.Column(db.Text)
    column_comment6 = db.Column(db.Text)
    column_comment7 = db.Column(db.Text)
    creation_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                              server_default=db.func.clock_timestamp(), comment='创建时间')

    def to_dict(self):
        return {
            'result_id': self.result_id,
            'comp_task_id': self.comp_task_id,
            'comp_task_name': self.comp_task_name,
            'schema': self.schema,
            'table_name': self.table_name,
            'column_name': self.column_name,
            'total_check_result': self.total_check_result,
            'table_name_check_result': self.table_name_check_result,
            'table_name1': self.table_name1,
            'table_name2': self.table_name2,
            'table_name3': self.table_name3,
            'table_name4': self.table_name4,
            'table_name5': self.table_name5,
            'table_name6': self.table_name6,
            'table_name7': self.table_name7,
            'column_name_check_result': self.column_name_check_result,
            'column_name1': self.column_name1,
            'column_name2': self.column_name2,
            'column_name3': self.column_name3,
            'column_name4': self.column_name4,
            'column_name5': self.column_name5,
            'column_name6': self.column_name6,
            'column_name7': self.column_name7,
            'data_type_check_result': self.data_type_check_result,
            'data_type1': self.data_type1,
            'data_type2': self.data_type2,
            'data_type3': self.data_type3,
            'data_type4': self.data_type4,
            'data_type5': self.data_type5,
            'data_type6': self.data_type6,
            'data_type7': self.data_type7,
            'data_default_check_result': self.data_default_check_result,
            'data_default1': self.data_default1,
            'data_default2': self.data_default2,
            'data_default3': self.data_default3,
            'data_default4': self.data_default4,
            'data_default5': self.data_default5,
            'data_default6': self.data_default6,
            'data_default7': self.data_default7,
            'pkey_check_result': self.pkey_check_result,
            'pkey1': self.pkey1,
            'pkey2': self.pkey2,
            'pkey3': self.pkey3,
            'pkey4': self.pkey4,
            'pkey5': self.pkey5,
            'pkey6': self.pkey6,
            'pkey7': self.pkey7,
            'nullable_check_result': self.nullable_check_result,
            'nullable1': self.nullable1,
            'nullable2': self.nullable2,
            'nullable3': self.nullable3,
            'nullable4': self.nullable4,
            'nullable5': self.nullable5,
            'nullable6': self.nullable6,
            'nullable7': self.nullable7,
            'table_comment_check_result': self.table_comment_check_result,
            'table_comment1': self.table_comment1,
            'table_comment2': self.table_comment2,
            'table_comment3': self.table_comment3,
            'table_comment4': self.table_comment4,
            'table_comment5': self.table_comment5,
            'table_comment6': self.table_comment6,
            'table_comment7': self.table_comment7,
            'column_comment_check_result': self.column_comment_check_result,
            'column_comment1': self.column_comment1,
            'column_comment2': self.column_comment2,
            'column_comment3': self.column_comment3,
            'column_comment4': self.column_comment4,
            'column_comment5': self.column_comment5,
            'column_comment6': self.column_comment6,
            'column_comment7': self.column_comment7,
            'creation_date': self.creation_date.strftime('%Y-%m-%d %H:%M:%S')
        }


class JobTableRepare(db.Model):
    """ 比对任务对表进行比对及修复表 """
    __tablename__ = 'dc_job_table_repare_t'
    __table_args__ = (
        PrimaryKeyConstraint('job_table_repare_id', name='dc_job_table_repare_pk', comment='主键'),
        Index(
            'idx_dc_job_table_repare_1',
            'comp_task_id'
        ),
        Index(
            'idx_dc_job_table_repare_2',
            'comp_task_name'
        ),
        {'comment': '比对任务对表进行比对及修复表', },
    )
    job_table_repare_id = db.Column(
        db.BigInteger,
        Sequence('dc_job_table_repare_s'),
        primary_key=True,
        server_default=Sequence('dc_job_table_repare_s').next_value(),
        nullable=False,
        comment='主键ID'
    )
    comp_task_id = db.Column(db.BigInteger, comment='数据库比对任务ID')
    comp_task_name = db.Column(db.String(300), comment='数据库比对任务名称')
    schema = db.Column(db.String(100), comment='数据库schema')
    table_name = db.Column(db.String(100), comment='表名')
    table_name_check_result = db.Column(db.String(1),
                                        comment='检查表。比对任务中的所有数据源是否都包含该表，如果包含，则“√”，否则“×”。')
    table_name1 = db.Column(db.String(1), comment='数据源1的表是否存在，存在“√”，否则“×”。')
    table_name2 = db.Column(db.String(1), comment='数据源2的表是否存在，存在“√”，否则“×”。')
    compare_scripts_of_source2 = db.Column(db.Text, comment='数据源2修复脚本')
    table_name3 = db.Column(db.String(1), comment='数据源3的表是否存在，存在“√”，否则“×”。')
    compare_scripts_of_source3 = db.Column(db.Text, comment='数据源3修复脚本')
    table_name4 = db.Column(db.String(1), comment='数据源4的表是否存在，存在“√”，否则“×”。')
    compare_scripts_of_source4 = db.Column(db.Text, comment='数据源4修复脚本')
    table_name5 = db.Column(db.String(1), comment='数据源5的表是否存在，存在“√”，否则“×”。')
    compare_scripts_of_source5 = db.Column(db.Text, comment='数据源5修复脚本')
    table_name6 = db.Column(db.String(1), comment='数据源6的表是否存在，存在“√”，否则“×”。')
    compare_scripts_of_source6 = db.Column(db.Text, comment='数据源6修复脚本')
    table_name7 = db.Column(db.String(1), comment='数据源7的表是否存在，存在“√”，否则“×”。')
    compare_scripts_of_source7 = db.Column(db.Text, comment='数据源7修复脚本')
    creation_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                              server_default=db.func.clock_timestamp(), comment='创建时间')

    def to_dict(self):
        return {
            'job_table_repare_id': self.job_table_repare_id,
            'comp_task_id': self.comp_task_id,
            'comp_task_name': self.comp_task_name,
            'schema': self.schema,
            'table_name': self.table_name,
            'table_name_check_result': self.table_name_check_result,
            'table_name1': self.table_name1,
            'table_name2': self.table_name2,
            'compare_scripts_of_source2': self.compare_scripts_of_source2,
            'table_name3': self.table_name3,
            'compare_scripts_of_source3': self.compare_scripts_of_source3,
            'table_name4': self.table_name4,
            'compare_scripts_of_source4': self.compare_scripts_of_source4,
            'table_name5': self.table_name5,
            'compare_scripts_of_source5': self.compare_scripts_of_source5,
            'table_name6': self.table_name6,
            'compare_scripts_of_source6': self.compare_scripts_of_source6,
            'table_name7': self.table_name7,
            'compare_scripts_of_source7': self.compare_scripts_of_source7,
            'creation_date': self.creation_date.strftime('%Y-%m-%d %H:%M:%S')
        }
